﻿using Dapper;
using Lifang.Common;
using Lifang.Entites;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Data;

namespace Lifang.Dao
{
    public class DapperHelper
    {
        private static readonly string DbName = "lifangdb";

        public string ConnStr = "Server=47.98.187.80;uid=root;pwd=zywmg121603!@#;database=LifangDB;SslMode = none;Charset=utf8";


        

        public IDbConnection CreateConnection(string dataBase, bool writeBase = false)
        {
            //string connectstring = ConfigurationManager.ConnectionStrings[dataBase].ToString();
            //ConnStr = "Server=10.102.118.36;uid=alan;pwd=123456;database=test;";
            string connectstring = ConnStr;
            IDbConnection connection = new MySqlConnection(connectstring);
            connection.Open();
            return connection;
        }


        #region 获取数据库链接
        /// <summary>
        /// 获取数据库链接
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="writeDataBase"></param>
        /// <returns></returns>
        public IDbConnection GetDBConnection(string dataBase, bool writeDataBase = false)
        {
            return CreateConnection(dataBase, writeDataBase);
        }
        #endregion

        #region 获取数据库链接
        /// <summary>
        /// 获取数据库链接
        /// </summary>
        /// <param name="dataBase"></param>
        /// <param name="writeDataBase"></param>
        /// <returns></returns>
        public IDbConnection GetDBConnection()
        {
            var dataBase = DbName;
            return CreateConnection(dataBase);
        }
        #endregion

        #region 根据主键查询表实体
        /// <summary>
        /// 根据主键查询表实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValue"></param>
        /// <param name="sqlNote"></param>
        /// <param name="writeDataBase"></param>
        /// <returns></returns>
        public T SelectModel<T>(int keyValue, SqlNote sqlNote, bool writeDataBase = false) where T : class, IBaseDbEntity<T>, new()
        {
            T tempT = new T();
            var Conn = GetDBConnection(DbName);
            using (Conn)
            {
                string pkName = tempT.GetPkName();
                string strsql = tempT.GetSelectSql() + " and " + tempT.GetPkName() + "=" + keyValue + (sqlNote != null ? sqlNote.ToString() : string.Empty);
                return Conn.Query<T>(strsql, null).ToList().FirstOrDefault();
            }
        }
        #endregion

        #region 单表条件查询单表实体
        /// <summary>
        /// 单表条件查询单表实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection"></param>
        /// <param name="sqlNote"></param>
        /// <param name="writeDataBase"></param>
        /// <returns></returns>
        public List<T> SelectList<T>(NameValueCollection collection, SqlNote sqlNote, DataPage dp = null, bool writeDataBase = false) where T : class, IBaseDbEntity<T>, new()
        {
            T tempT = new T();
            var Conn = GetDBConnection(DbName);
            using (Conn)
            {
                string strWhere = DALUnitity.GetsqlWhere(collection);
                string strsql = tempT.GetSelectSql();
                if (!string.IsNullOrEmpty(strWhere))
                {
                    strsql += " AND " + strWhere;
                }
                strsql += (sqlNote != null ? sqlNote.ToString() : string.Empty);
                if (dp != null)
                {
                    dp.OrderField = string.IsNullOrWhiteSpace(dp.OrderField) ? tempT.GetPkName() : dp.OrderField;
                    strsql += string.Format(" ORDER BY  {0}  limit {1},{2}", dp.OrderField, (dp.PageIndex - 1) * dp.PageSize, dp.PageSize);
                }
                return Conn.Query<T>(strsql, null).ToList();
            }
        }
        #endregion

        #region 单表条件查询单表实体
        /// <summary>
        /// 单表条件查询单表实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection"></param>
        /// <param name="sqlNote"></param>
        /// <param name="writeDataBase"></param>
        /// <returns></returns>
        public T SelectModel<T>(NameValueCollection collection, SqlNote sqlNote, bool writeDataBase = false) where T : class, IBaseDbEntity<T>, new()
        {
            T tempT = new T();
            var Conn = GetDBConnection(DbName);
            using (Conn)
            {
                string strWhere = DALUnitity.GetsqlWhere(collection);
                string strsql = tempT.GetSelectSql() + " AND " + strWhere + (sqlNote != null ? sqlNote.ToString() : string.Empty);
                return Conn.Query<T>(strsql, null).ToList().FirstOrDefault();
            }
        }
        #endregion

        #region 实体集合查询
        /// <summary>
        /// 根据sql查询实体集合
        /// 主要用于单表集合查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strsql"></param>
        /// <param name="dataBase"></param>
        /// <param name="sqlNote"></param>
        /// <param name="param"></param>
        /// <param name="writeDataBase"></param>
        /// <returns></returns>
        public List<T> QueryList<T>(string strsql, SqlNote sqlNote, object param = null, bool writeDataBase = false)
        {
            strsql += sqlNote != null ? sqlNote.ToString() : string.Empty;
            var Conn = GetDBConnection(DbName);
            using (Conn)
            {
                return Conn.Query<T>(strsql, param).ToList();
            }
        }
        #endregion

        #region 多表联查
        /// <summary>
        /// 适用于多表联查
        /// 返回数据不确定的情况使用
        /// </summary>
        /// <param name="strsql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<dynamic> QueryList(string strsql, string dataBase, SqlNote sqlNote, object param = null, bool writeDataBase = false)
        {
            strsql += sqlNote != null ? sqlNote.ToString() : string.Empty;
            var Conn = GetDBConnection(dataBase, writeDataBase);
            using (Conn)
            {
                return Conn.Query(strsql, param).ToList();
            }
        }
        #endregion

        #region 增删改操作
        /// <summary>
        /// 增删改操作
        /// </summary>
        /// <param name="strsql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public long Execute(string strsql, SqlNote sqlNote, object param = null)
        {
            var Conn = GetDBConnection(DbName);
            using (Conn)
            {
                strsql += sqlNote != null ? sqlNote.ToString() : string.Empty;
                return Conn.Execute(strsql, param);
            }
        }
        #endregion

        public int UpdateExecute<T>(T model, SqlNote sqlNote, List<string> mustUpdate = null) where T : class, IBaseDbEntity<T>, new()
        {
            var Conn = GetDBConnection(DbName);
            using (Conn)
            {
                var pm = GetNeedUpdateDic(model, mustUpdate); //此不能取变化的字段

                //取不等于默认值的字值

                string sql = "Update " + model.GetDbName() + "." + model.GetTableName() + " SET ";

                var fieldList = new List<string>();

                foreach (var p in pm)
                {
                    fieldList.Add(string.Format("{0}=@{1}", p.Key, p.Key));
                }
                sql += string.Join(",", fieldList);
                sql += string.Format(" WHERE {0}={1}", model.GetPkName(), model.GetPkValue());
                return Conn.Execute(sql, pm);
            }
        }

        public int UpdateOnlyFieldExecute<T>(T model, SqlNote sqlNote, string fieldName, string fieldValue) where T : class, IBaseDbEntity<T>, new()
        {
            var Conn = GetDBConnection(DbName);
            using (Conn)
            {
                var pm = new Dictionary<string, object>() { };
                pm.Add(fieldName, fieldValue);

                string sql = "Update " + model.GetDbName() + "." + model.GetTableName() + " SET ";

                var fieldList = new List<string>();

                sql += string.Format("{0}=@{0}", fieldName);
                sql += string.Format(" WHERE {0}={1}", model.GetPkName(), model.GetPkValue());
                return Conn.Execute(sql, pm);
            }
        }


        #region 单表新增
        /// <summary>
        /// 单表新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="dataBase"></param>
        /// <param name="sqlNote"></param>
        /// <returns></returns>
        public int InserData<T>(T model, SqlNote sqlNote) where T : class, IBaseDbEntity<T>, new()
        {
            var Conn = GetDBConnection(model.GetDbName(), true);
            using (Conn)
            {
                string insertSQL = model.GetInsertSql();
                int i = Conn.Execute(insertSQL, model);
                return i;
            }
        }
        #endregion

        #region 带事务的增删改操作
        /// <summary>
        /// 带事务的增删改操作
        /// </summary>
        /// <param name="strsql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Execute(string strsql, string dataBase, IDbTransaction trans, SqlNote sqlNote, object param = null)
        {
            var Conn = GetDBConnection(dataBase, true);
            using (Conn)
            {
                strsql += sqlNote != null ? sqlNote.ToString() : string.Empty;
                return Conn.Execute(strsql, null, trans);
            }
        }
        #endregion

        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storeProcedure">存储过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public int ExecuteStoredProcedure(string storeProcedure, string dataBase, SqlNote sqlNote, object param = null, bool writeDataBase = true)
        {
            var Conn = GetDBConnection(dataBase, writeDataBase);
            using (Conn)
            {
                return Conn.Execute(storeProcedure, null, commandType: CommandType.StoredProcedure);
            }
        }
        #endregion

        #region 获取要更新的字段
        /// <summary>
        /// 获取要更新的字段   1. 不等于默认值   2.指定MustUpdate
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="current"></param>
        /// <param name="mustUpdate"></param>
        /// <returns></returns>
        public static Dictionary<string, object> GetNeedUpdateDic<T>(T current, List<string> mustUpdate = null) where T : class, IBaseDbEntity<T>, new()
        {
            if (current == null)
            {
                return new Dictionary<string, object>(); //比较的两个类需类型一样
            }
            var origin = new T();
            var cur = current as IBaseDbEntity<T>;
            cur.SetDefaultValue();
            var showFields = cur.GetFieldNotes();
            PropertyInfo[] propertys = current.GetType().GetProperties();
            var returnList = new Dictionary<string, object>();
            foreach (var pi in propertys)
            {
                if (pi.Name != current.GetPkName()) //如过存在，进入比对 
                {
                    var hasChange = pi.GetValue(current, null) != null && origin.GetType().GetProperty(pi.Name).GetValue(origin, null) != null && pi.GetValue(current, null).ToString() != origin.GetType().GetProperty(pi.Name).GetValue(origin, null).ToString();
                    if (hasChange || (mustUpdate != null && mustUpdate.Contains(pi.Name)))
                    {
                        returnList.Add(pi.Name, pi.GetValue(current, null));
                    }
                }
            }
            return returnList;
        }
        #endregion


    }
}
